SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE VIEW [dbo].[Duplicate_SSB_CUSIPS] AS
SELECT  m2.FS_ID ,
        m2.SSB_ID SSB_Unique_Cusip,
		mdups.ssbGroupedCusip
		
		 FROM ReportingScripts.dbo.SSB_CUSIP_Map m2	

CROSS APPLY(

SELECT  map.FS_ID ,
        COUNT(*) cnt, 
		MAX(map.SSB_ID) ssbGroupedCusip 
		  FROM  ReportingScripts.dbo.SSB_CUSIP_Map map
		GROUP BY map.FS_ID /*HAVING COUNT(*)>1 */) mdups


WHERE mdups.FS_ID  = m2.FS_ID

GO
